from collections import defaultdict

import xlrd

wb = xlrd.open_workbook(
    'E:/群晖备份/SynologyDrive/kpmg/长沙银行/demo/v9.xls')

columns_str = """ADDDEDUCTMARKS, ADDTYPE, ASSGID, C_1, C_10, C_11, C_12, C_13, C_14, C_15, C_16, C_17, C_18, C_19,
                     C_2, C_20, C_3, C_4, C_5, C_6, C_7, C_8, C_9, CACULATEFLAG, CACULATETIME, CHECKFLAG, CPAYDATE,
                     CPERIOD, CURRENCYRATE, CYEAR, CYEARPERIOD, D_1, D_10, D_2, D_3, D_4, D_5, D_6, D_7, D_8, D_9,
                     DERATEPTG, DR, EXPENSE_DEDUCTION, F_1, F_10, F_100, F_11, F_12, F_13, F_14, F_15, F_16, F_17, F_18,
                     F_19, F_2, F_20, F_21, F_22, F_23, F_24, F_25, F_26, F_27, F_28, F_29, F_3, F_30, F_31, F_32, F_33,
                     F_34, F_35, F_36, F_37, F_38, F_39, F_4, F_40, F_41, F_42, F_43, F_44, F_45, F_46, F_47, F_48,
                     F_49, F_5, F_50, F_51, F_52, F_53, F_54, F_55, F_56, F_57, F_58, F_59, F_6, F_60, F_61, F_62, F_63,
                     F_64, F_65, F_66, F_67, F_68, F_69, F_7, F_70, F_71, F_72, F_73, F_74, F_75, F_76, F_77, F_78,
                     F_79, F_8, F_80, F_81, F_82, F_83, F_84, F_85, F_86, F_87, F_88, F_89, F_9, F_90, F_91, F_92, F_93,
                     F_94, F_95, F_96, F_97, F_98, F_99, FIPDEPTVID, FIPENDFLAG, FIPORGVID, ISDERATE, ISNDEBUCT,
                     ISRULEHINT, LIBDEPTVID, NQUICKDEBUCT, PARTFLAG, PK_BANKACCBAS1, PK_BANKACCBAS2, PK_BANKACCBAS3,
                     PK_BANKTYPE1, PK_BANKTYPE2, PK_BANKTYPE3, PK_FINANCEDEPT, PK_FINANCEORG, PK_GROUP,
                     PK_LIABILITYDEPT, PK_LIABILITYORG, PK_ORG, PK_PSNDOC, PK_PSNJOB, PK_PSNORG, PK_TAX_CLASS,
                     PK_WA_CLASS, PK_WA_DATA, PK_WA_TAXSETBASE, PREWADATA, REDTOTAL, STOPFLAG, TAXABLE_INCOME,
                     TAXDECLAREORG, TAXDECLAREORGVID, TAXPERIOD, TAXRATE, TAXSUMUID, TAXTABLEID, TAXTYPE, TAXYEAR, TS,
                     VPAYCOMMENT, WORKDEPT, WORKDEPTVID, WORKORG, WORKORGVID, f_101, f_102, f_103, f_104, f_105, f_106,
                     f_107, f_108, f_109, f_110, f_111, f_112, f_113, f_114, f_115, f_116, f_117, f_118, f_119, f_120,
                     f_121, f_122, f_123, f_124, f_125, f_126, f_127, f_128, f_129, f_130, f_131, f_132, f_133, f_134,
                     f_135, f_136, f_137, f_138, f_139, f_140, f_141, f_142, f_143, f_144, f_145, f_146, f_147, f_148,
                     f_149, f_150, f_151, f_152, f_153, f_154, f_155, f_156, f_157, f_158, f_159, f_160, f_161, f_162,
                     f_163, f_164, f_165, f_166, f_167, f_168, f_169, f_170, f_171, f_172, f_173, f_174, f_175, f_176,
                     f_177, f_178, f_179, f_180, f_181, f_182, f_183, f_184, f_185, f_186, f_187, f_188, f_189, f_190,
                     f_191, f_192, f_193, f_194, f_195, f_196, f_197, f_198, f_199, f_200, f_201, f_202, f_203, f_204,
                     f_205, f_206, f_207, f_208, f_209, f_210, f_211, f_212, f_213, f_214, f_215, f_216, f_217, f_218,
                     f_219, pay_status, trans_code, pk_accorgid, pk_bugorgid, pay_count, f_220, f_221, f_222, f_223,
                     f_224, f_225, f_226, f_227, f_228, f_229, f_230, f_231, f_232, f_233, f_234, f_235, f_236, f_237,
                     f_238, f_239"""

values_str = """'~', '1', 1, null, '', '', null, '否', null, '资产保全部', null, null, null, null, null, null, null, null, null,
        null, '奖励', '', '', 'Y', null, 'Y', null, '05', null, '2023', '202305', null, null, null, null, null, null,
        null, null, null, null, null, 0, null, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, '0001A51000000000732F', 'N', '0001A510000000005SIY', 'N', 'N',
        'Y', '~', null, 'N', null, null, null, null, null, null, '1001A510000000006NMZ', '0001A510000000005SIZ',
        '0001A510000000000YQM', '~', '~', '0001A510000000008KNV', 'BDPSNDOC824A7AAED3D2', 'HIPSNJOB8A79B216D3D2',
        'HIPSNORG8320FCC1D3D2', '~', '1001A510000000011IUM', '0001A51000000000F7Z3', '~', null, 0.00000000, 'N', null,
        '0001A510000000005SIZ', '0001A510000000005SIY', '05', null, '1001A510000000024QWH', '~', '0', '2023',
        '2023-04-07 09:41:43', null, '1001A510000000006NMZ', '0001A51000000000732F', '0001A510000000005SIZ',
        '0001A510000000005SIY', 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, -1000.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 1.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, '5', null, '0001A510000000008KNV', '0001A510000000008KNV', 1,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000, 0.00000000,
        0.00000000, 0.00000000"""

insert = 'INSERT INTO wa_data (' + columns_str + ')VALUES (' + values_str + ');'

pre_header = 'salunitId@_@billCode@_@clerkCode@_@accorgId@_@bugorgId@_@cyear@_@cperiod@_@jobglbdef23'
pre_value = '{salunitId}@_@{billCode}@_@{table_no}@_@{accorgId}@_@{bugorgId}@_@2023@_@04@_@{is_school}'


# salunitId,accorgId,bugorgId 都用10012
def isEmpty(str):
    if str is None or str == '' or str == '\r' or str == '\n' or str == '\r\n':
        return True
    return False


def process():
    # salunitId @ _ @ billCode @ _ @ clerkCode @ _ @ name @ _ @ accorgId @ _ @ bugorgId @ _ @ cyear @ _ @ cperiod @ _ @ jobglbdef23
    # salunitId 发薪机构,要转换
    # billCode 单机编码,随便早
    # clerkCode 工号
    # name 姓名
    # accorgId 固定值
    # bugorgId 固定值
    # jobglbdef23 是否校招生
    # // 总行月度薪酬
    # ZHYDXC("001", "总行", "P_INFA_BSC_CONTRACT_INFO"),
    # // 分支行月度薪酬
    # FZHYDXC("002", "分支行", "P_INFA_BSC_CONTRACT_SUBORG_INFO"),
    # // 退休月度薪酬
    # TXYDXC("004", "退休", "P_INFA_BSC_RETIRE_INFO"),
    # // 内退月度薪酬
    # NTYDXC("005", "内退", "P_INFA_BSC_INTERNAL_RETIRE_INFO"),
    # // 月度绩效方案
    # JXXC("006", "绩效", "P_INFA_BSC_PER_CONTRACT_INFO"),
    # // 福利薪酬方案
    # FLXC("011", "福利", "P_INFA_WELFARE_INFO"),
    # // 合同工专项及营销费用方案
    # ZXJYY("008", "专项及营销", "P_INFA_SPECIAL_CONTRACT_INFO"),

    # # ['总行月度薪酬', '分支行月度薪酬', '合同工绩效薪酬', '退休员工月度薪酬', '内退员工月度薪酬', '数据', '工资项']
    salary_mapping_sheet = wb.sheet_by_index(5)
    col_name, name_col = get_salary_mapping(salary_mapping_sheet)
    # print(mapping)

    # sheet0 = wb.sheet_by_index(0)
    check_mapping([(0, 1), (1, 1), (2, 1), (3, 1), (4, 1)], name_col)
    # check_mapping([(2, 1)], name_col)
    # columns = columns_str.replace('\n', '').replace(' ', '').replace('\n                     ', '').split(",")
    # print(columns)
    # values = values_str.replace('\n', '').replace(' ', '').replace('\n                     ', '').split(",")
    # print(values)

    # for pos, column_ in enumerate(columns):
    #     column = column_.lower();
    #     if column.startswith('f_') or column.startswith('c_'):
    #         name = col_name[column]
    #         print('%s->%s', column, name)
    #         # values[pos] = ' %' + column + '%'
    #
    # print(''.join(values))

    # table = wb.sheet_by_index(0)
    # row_header = table.row_values(2, start_colx=0, end_colx=None)
    # for name in row_header:
    #     col = mapping[name]
    #     print(col)

    out_root0 = 'E:/群晖备份/SynologyDrive/kpmg/长沙银行/demo/P_INFA_BSC_CONTRACT_INFO_{billCode}_01.dat'
    process0(wb.sheet_by_index(0), col_name, name_col, out_root0, 1, 'XZFFSQ2023040001')

    out_root1 = 'E:/群晖备份/SynologyDrive/kpmg/长沙银行/demo/P_INFA_BSC_CONTRACT_SUBORG_INFO_{billCode}_02.dat'
    process0(wb.sheet_by_index(1), col_name, name_col, out_root1, 1, 'XZFFSQ2023040002')
    #
    out_root2 = 'E:/群晖备份/SynologyDrive/kpmg/长沙银行/demo/P_INFA_BSC_PER_CONTRACT_INFO_{billCode}_03.dat'
    process0(wb.sheet_by_index(2), col_name, name_col, out_root2, 1, 'XZFFSQ2023040003')
    #
    out_root3 = 'E:/群晖备份/SynologyDrive/kpmg/长沙银行/demo/P_INFA_BSC_RETIRE_INFO_{billCode}_04.dat'
    process0(wb.sheet_by_index(3), col_name, name_col, out_root3, 1, 'XZFFSQ2023040004')
    # # #
    out_root4 = 'E:/群晖备份/SynologyDrive/kpmg/长沙银行/demo/P_INFA_BSC_INTERNAL_RETIRE_INFO_{billCode}_05.dat'
    process0(wb.sheet_by_index(4), col_name, name_col, out_root4, 1, 'XZFFSQ2023040005')

    # salunitId@_@billCode@_@clerkCode@_@name@_@accorgId@_@bugorgId@_@cyear@_@cperiod@_@jobglbdef23


def process0(table, col_name, name_col, out_root, header_index, billCode):
    header = table.row_values(header_index, start_colx=0, end_colx=None)
    header_mapping = defaultdict(str)
    f_col = []
    empid_index = 0
    name_index = 0
    is_school_index = 0
    table_no_index = 0

    accorgId_index = 0
    bugorgId_index = 0
    salunitId_index = 0
    for pos, name in enumerate(header):
        col = name_col[name]
        if name == '员工号':
            empid_index = pos
        elif name == '员工名':
            name_index = pos
        elif name == '制单人工号':
            table_no_index = pos
        elif name == '发薪单位':
            salunitId_index = pos
        elif name == '记账机构':
            accorgId_index = pos
        elif name == '预算机构':
            bugorgId_index = pos
        elif name == '是否校招生':
            is_school_index = pos
        elif not isEmpty(col):
            f_col.append(col)
            header_mapping[pos] = col
        # else:
        # print(col)
    header_str = pre_header + '@_@' + '@_@'.join(f_col)
    out_root = out_root.format(billCode=billCode)
    with open(out_root, 'w', encoding='utf-8') as f:
        f.write(header_str + '\r')
        for index in range(header_index + 1, table.nrows):
            # for index in range(3, 4):
            data_cols = table.row_values(index, start_colx=0, end_colx=None)
            values = []
            empid = format_not_number(data_cols[empid_index])
            name = data_cols[name_index]
            table_no = format_not_number(data_cols[table_no_index])
            is_school = data_cols[is_school_index]
            accorgId = format_not_number(data_cols[accorgId_index])
            bugorgId = format_not_number(data_cols[bugorgId_index])
            salunitId = format_not_number(data_cols[salunitId_index])
            # dept = data_cols[4]
            # type = data_cols[5]
            # if not isEmpty(empid):
            for pos, value in enumerate(data_cols):
                col = header_mapping[pos]
                if not isEmpty(col):
                    values.append(str(value))
            pre_value_ = pre_value.format(empid=empid, name=name, is_school=is_school, billCode=billCode,
                                          table_no=table_no, accorgId=accorgId, bugorgId=bugorgId, salunitId=salunitId)
            value_str = pre_value_ + '@_@' + '@_@'.join(values)
            f.write(value_str + '\r')
    # 总行月度薪酬


def format_not_number(val):
    return str(val).replace('.0', '')


def check_mapping(sheets, mapping):
    ignores = ['', '扣罚种类', '制单人工号', '制单人姓名', '所在部门', '员工类型', '组织', '所在部门', '人员类型',
               '发薪单位', '记账机构', '预算机构', '是否校招生', '文件号']
    errors = []
    for sheet_content in sheets:
        index = sheet_content[0]
        header_row = sheet_content[1]
        sheet = wb.sheet_by_index(index)
        for col in range(sheet.ncols):
            value = sheet.cell(header_row, col).value
            value = value.replace('\n', '').strip()
            column = mapping[value]
            if isEmpty(column) and value not in ignores:
                info = 'value:{value}-->index:{index}-->col:{col}'
                info = info.format(value=value, index=index, col=col)
                # print(info)
                errors.append(value)
    errors = list(set(errors))
    for e in errors:
        print(e)
    # assert len(errors) > 0
    # "有工资项未被映射."


def get_salary_mapping(sheet):
    col_name = defaultdict(str)
    name_col = defaultdict(str)
    for row in range(sheet.nrows):
        column = sheet.cell(row, 0).value.strip()
        name = sheet.cell(row, 1).value.strip()
        col_name[column] = name
        name_col[name] = column
    return col_name, name_col


if __name__ == '__main__':
    process()

# # 使用xlrd模块的open_workbook函数打开指定Excel文件并获得Book对象（工作簿）
# wb = xlrd.open_workbook('E:\群晖备份\SynologyDrive\kpmg\长沙银行\副本费控全量项目记账测试造项目后台模拟数据表(2).xls')
# # 通过Book对象的sheet_names方法可以获取所有表单名称
# sheetnames = wb.sheet_names()
# print(sheetnames)
# # 通过指定的表单名称获取Sheet对象（工作表）
# sheet = wb.sheet_by_name(sheetnames[0])
# # 通过Sheet对象的nrows和ncols属性获取表单的行数和列数
# print(sheet.nrows, sheet.ncols)
# for row in range(sheet.nrows):
#     for col in range(sheet.ncols):
#         # 通过Sheet对象的cell方法获取指定Cell对象（单元格）
#         # 通过Cell对象的value属性获取单元格中的值
#         value = sheet.cell(row, col).value
#         # 对除首行外的其他行进行数据格式化处理
#         if row > 0:
#             # 第1列的xldate类型先转成元组再格式化为“年月日”的格式
#             if col == 0:
#                 print(value)
#                 # xldate_as_tuple函数的第二个参数只有0和1两个取值
#                 # 其中0代表以1900-01-01为基准的日期，1代表以1904-01-01为基准的日期
#                 # value = xlrd.xldate_as_tuple(value, 0)
#                 # value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'
#             # 其他列的number类型处理成小数点后保留两位有效数字的浮点数
#             else:
#                 print(value)
#         print(value, end='\t')
#     print()
# # 获取最后一个单元格的数据类型
# # 0 - 空值，1 - 字符串，2 - 数字，3 - 日期，4 - 布尔，5 - 错误
# last_cell_type = sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1)
# print(last_cell_type)
# # 获取第一行的值（列表）
# print(sheet.row_values(0))
# # 获取指定行指定列范围的数据（列表）
# # 第一个参数代表行索引，第二个和第三个参数代表列的开始（含）和结束（不含）索引
# print(sheet.row_slice(3, 0, 5))
